package openase.audio;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections.map.LRUMap;
import org.apache.commons.dbutils.DbUtils;

public class H2Storage extends Storage {

	private static final String DB_URL = "jdbc:h2:file:./audiobase/db;AUTOCOMMIT=OFF";
	private static final String DB_USER = "sa";
	private static final String DB_PSWD = "audiodb";

	private static final Connection conn;

	static {
		try {
			Class.forName("org.h2.Driver");

			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PSWD);
			consistDatabase();
		} catch (Throwable t) {
			throw new RuntimeException("Problems connecting to the database: " + t.getMessage());
		}
	}

	private LRUMap audioLruCache = new LRUMap(300);

	private AudioInfo getCached(int id) {
		return (AudioInfo) audioLruCache.get(id);
	}

	private void cache(AudioInfo audio) {
		audioLruCache.put(audio.getId(), audio);
	}

	private static void consistDatabase() throws SQLException {
		if (!existsTable(AUDIO_TABLE_NAME)) {
			executeDDL(conn, CREATE_AUDIO_TABLE);
		}
		if (!existsTable(FINGERPRINT_TABLE_NAME)) {
			executeDDL(conn, CREATE_FINGERPRINT_TABLE);
			executeDDL(conn, CREATE_INDEX_FINGERPRINT_HASH);
		}
	}

	private static boolean existsTable(String tableName) throws SQLException {
		ResultSet tables = conn.getMetaData().getTables(null, null, tableName, null);
		try {
			return tables.next();
		} finally {
			DbUtils.closeQuietly(tables);
		}
	}

	protected int lastAudioId() {
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_AUDIO_MAX_ID);

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					if (rs.next()) {
						int max = rs.getInt(1);
						return max;
					}
					return 0;
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}
		} catch (SQLException sqle) {
			return 0;
		}
	}

	@Override
	public void addAudio(AudioInfo audio) throws StorageException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(INSERT_INTO_AUDIO);
			stmt.setString(1, audio.getInfo());
			if (executeDML(stmt)) {
				conn.commit();
				int id = getGeneratedKey(stmt);
				audio.setId(id);// update id
				cache(audio);
			} else {
				throw new SQLException("Insert of audio " + audio + " failed.");
			}
		} catch (SQLException sqle) {
			throw wrap(sqle);
		} finally {
			DbUtils.closeQuietly(stmt);
		}
	}

	@Override
	public void addFingerprint(AudioFingerprint fingerprint) throws StorageException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(INSERT_INTO_FINGERPRINT);
			stmt.setInt(1, fingerprint.getSource().getId());
			stmt.setInt(2, fingerprint.getOffset());
			stmt.setLong(3, fingerprint.getHash());

			executeDML(stmt);
			conn.commit();
		} catch (SQLException sqle) {
			throw wrap(sqle);
		} finally {
			DbUtils.closeQuietly(stmt);
		}
	}

	@Override
	public AudioInfo getAudio(int id) throws StorageException {
		AudioInfo audio = getCached(id);
		if (audio != null) {
			return audio;
		}

		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_AUDIO_BY_ID);
			stmt.setInt(1, id);

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					if (rs.next()) {
						String name = rs.getString(1);
						audio = newAudio(id, name);
						cache(audio);
						return audio;
					}
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}
			return null;
		} catch (SQLException sqle) {
			throw wrap(sqle);
		}
	}

	@Override
	public AudioInfo getAudio(String name) throws StorageException {
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_AUDIO_BY_NAME);
			stmt.setString(1, name);

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					if (rs.next()) {
						int id = rs.getInt(1);
						AudioInfo audio = newAudio(id, name);
						cache(audio);
						return audio;
					}
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}
			return null;
		} catch (SQLException sqle) {
			throw wrap(sqle);
		}
	}

	private static int getGeneratedKey(PreparedStatement stmt) throws SQLException {
		ResultSet keysRs = stmt.getGeneratedKeys();
		try {
			if (keysRs.next()) {
				return keysRs.getInt(1);
			}
			throw new SQLException("No keys generated for audio.");
		} finally {
			DbUtils.closeQuietly(keysRs);
		}
	}

	@Override
	public AudioFingerprint[] getMatchingFingerprints(long hash) throws StorageException {
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_FINGERPRINT_BY_HASH);
			stmt.setLong(1, hash);

			final List<AudioFingerprint> fps = new ArrayList<AudioFingerprint>(100);

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					while (rs.next()) {
						int audioId = rs.getInt(1);
						int offset = rs.getInt(2);

						AudioFingerprint fp = new AudioFingerprint(getAudio(audioId), offset, hash);
						fps.add(fp);
					}
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}

			return fps.toArray(new AudioFingerprint[0]);
		} catch (SQLException sqle) {
			throw wrap(sqle);
		}
	}

	@Override
	public int getAudioCount() throws StorageException {
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_AUDIO_COUNT);

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					if (rs.next()) {
						int max = rs.getInt(1);
						return max;
					}
					return 0;
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}
		} catch (SQLException sqle) {
			throw wrap(sqle);
		}
	}

	@Override
	public int getFingerprintCount() throws StorageException {
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_FINGERPRINT_COUNT);

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					if (rs.next()) {
						int max = rs.getInt(1);
						return max;
					}
					return 0;
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}
		} catch (SQLException sqle) {
			throw wrap(sqle);
		}
	}

	@Override
	public int getFingerprintCount(AudioInfo audio) throws StorageException {
		try {
			PreparedStatement stmt = conn.prepareStatement(SELECT_FINGERPRINT_COUNT_BY_ID);
			stmt.setInt(1, audio.getId());

			try {
				ResultSet rs = stmt.executeQuery();
				try {
					if (rs.next()) {
						int max = rs.getInt(1);
						return max;
					}
					return 0;
				} finally {
					DbUtils.closeQuietly(rs);
				}
			} finally {
				DbUtils.closeQuietly(stmt);
			}
		} catch (SQLException sqle) {
			throw wrap(sqle);
		}
	}

	private static StorageException wrap(Throwable t) {
		return new StorageException(t);
	}

	private static boolean executeDDL(Connection conn, String cmd) throws SQLException {
		PreparedStatement stmt = conn.prepareStatement(cmd);
		try {
			return stmt.execute();
		} finally {
			DbUtils.closeQuietly(stmt);
		}
	}

	private static boolean executeDML(PreparedStatement stmt) throws SQLException {
		int ret = stmt.executeUpdate();
		return ret >= 1;
	}

	private static final String AUDIO_TABLE_NAME = "AUDIO";
	private static final String FINGERPRINT_TABLE_NAME = "FINGERPRINT";

	// -------- CREATES --------

	private static final String CREATE_AUDIO_TABLE = //
	"CREATE TABLE " + AUDIO_TABLE_NAME + "(" + //
			"id INTEGER AUTO_INCREMENT NOT NULL," + //
			"name VARCHAR(100) NOT NULL UNIQUE," + //
			"PRIMARY KEY (id)" + //
			");";

	private static final String CREATE_FINGERPRINT_TABLE = //
	"CREATE TABLE " + FINGERPRINT_TABLE_NAME + "(" + //
			"id INTEGER NOT NULL," + //
			"offset INTEGER NOT NULL, " + //
			"hash BIGINT NOT NULL, " + //
			"PRIMARY KEY (id, offset)" + //
			");";

	private static final String CREATE_INDEX_FINGERPRINT_HASH = "CREATE INDEX idx_fp_hash ON " + FINGERPRINT_TABLE_NAME + " (hash);";

	// -------- INSERTS --------

	private static final String INSERT_INTO_AUDIO = "INSERT INTO " + AUDIO_TABLE_NAME + "(name) VALUES(?);";

	private static final String INSERT_INTO_FINGERPRINT = "INSERT INTO " + FINGERPRINT_TABLE_NAME + "(id, offset, hash) VALUES(?, ?, ?);";

	// -------- SELECTS --------

	private static final String SELECT_AUDIO_BY_ID = "SELECT name FROM " + AUDIO_TABLE_NAME + " WHERE id = ?;";

	private static final String SELECT_AUDIO_BY_NAME = "SELECT id FROM " + AUDIO_TABLE_NAME + " WHERE name = ?;";

	private static final String SELECT_AUDIO_COUNT = "SELECT COUNT(id) FROM " + AUDIO_TABLE_NAME + ";";

	private static final String SELECT_AUDIO_MAX_ID = "SELECT MAX(id) FROM " + AUDIO_TABLE_NAME + ";";

	private static final String SELECT_FINGERPRINT_BY_HASH = "SELECT id, offset FROM " + FINGERPRINT_TABLE_NAME + " WHERE hash = ?;";

	private static final String SELECT_FINGERPRINT_COUNT = "SELECT COUNT(*) FROM " + FINGERPRINT_TABLE_NAME + ";";

	private static final String SELECT_FINGERPRINT_COUNT_BY_ID = "SELECT COUNT(*) FROM " + FINGERPRINT_TABLE_NAME + " WHERE id = ?;";

}
